package com.picvik.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import com.picvik.util.MyLog;

public class DiscussionCommentsUtil {
	// DB related private variables
	private Connection connection = null;
	private PreparedStatement preparedStatement = null;
	private ResultSet resultSet = null;
	
	// public Utility methods on discussion comments table.
	
	// Insertion methods
	public void addNewDiscussionComment(DiscussionCommentsModel discussionComments){
		
		if(discussionComments == null){
			return;
		}
		// Insert new discussion comment.
		
		try {
			DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
    		Date date = new Date();
	        
    		//query string 
    		String qstring = "INSERT INTO picvik_discussion_comments (owner_id, discussion_id, comment, lastmodified) " +
    				"VALUES("+ discussionComments.getOwner_id() +  "," + discussionComments.getDiscussion_id() + ", '" + 
    				discussionComments.getComment() + "', '" + dateFormat.format(date) +  "' );";
    		//System.out.println(qstring);
    		MyLog.log(qstring);
    		
    		connection = com.picvik.util.MySqlConnection.getInstance().getConnection();
            
    		preparedStatement = connection.prepareStatement(qstring);
            
            preparedStatement.executeUpdate(qstring);
            
            preparedStatement.close();
            connection.close();
		
    	} catch (Exception e) {
    		e.printStackTrace();
    	}		
	}
	
	// Retrieval Methods
	
	// Get all comments of the discussion with given discussion id.
	public ArrayList<DiscussionCommentsModel> getDiscussionComment(int discussionId){
		
		ArrayList<DiscussionCommentsModel> resultDiscussionComments = new ArrayList<DiscussionCommentsModel>();

		try{
			
			// Most recent comment at end. so ASC
			String getdiscussionCommentQuery = "SELECT * FROM picvik_discussion_comments WHERE " +
					"discussion_id = "+ discussionId +" ORDER BY lastmodified ASC;";
			
			MyLog.log(getdiscussionCommentQuery);
			
			connection = com.picvik.util.MySqlConnection.getInstance().getConnection();
			
			preparedStatement = connection.prepareStatement(getdiscussionCommentQuery);
			resultSet = preparedStatement.executeQuery();
			
			while(resultSet.next()){
				DiscussionCommentsModel discussionCommentsModel = new DiscussionCommentsModel();
				
				
				discussionCommentsModel.setId(resultSet.getInt("id"));
				discussionCommentsModel.setOwner_id(resultSet.getInt("owner_id"));
				discussionCommentsModel.setUsername(UserModel.getUserNameFromID(resultSet.getInt("owner_id")));
				discussionCommentsModel.setDiscussion_id(resultSet.getInt("discussion_id"));
				discussionCommentsModel.setComment(resultSet.getString("comment"));
				discussionCommentsModel.setCreatedtime(resultSet.getTimestamp("createdtime"));
				discussionCommentsModel.setLastmodified(resultSet.getTimestamp("lastmodified"));
				
				resultDiscussionComments.add(discussionCommentsModel);
			}	
			
			connection.close();
			preparedStatement.close();
			resultSet.close();
				
			}catch(Exception ex){
				connection = null;
				preparedStatement = null;
				resultSet = null;
				
				ex.printStackTrace();
			}
		
		return resultDiscussionComments;			
	}
	
	// Get list of discussion comments by a given user.
	public ArrayList<DiscussionCommentsModel> getDiscussionCommentHavingOwnerAs(int ownerId){
		
		ArrayList<DiscussionCommentsModel> resultDiscussionComments = new ArrayList<DiscussionCommentsModel>();
		
		try{
		
			String getdiscussionCommentQuery = "SELECT * FROM picvik_discussion_comments WHERE " +
					"owner_id = "+ ownerId +" ORDER BY lastmodified DESC LIMIT 50;";
			
			MyLog.log(getdiscussionCommentQuery);
			
			connection = com.picvik.util.MySqlConnection.getInstance().getConnection();
			
			preparedStatement = connection.prepareStatement(getdiscussionCommentQuery);
			resultSet = preparedStatement.executeQuery();
			
			while(resultSet.next()){
				DiscussionCommentsModel discussionCommentsModel = new DiscussionCommentsModel();
				
				
				discussionCommentsModel.setId(resultSet.getInt("id"));
				discussionCommentsModel.setOwner_id(resultSet.getInt("owner_id"));
				discussionCommentsModel.setUsername(UserModel.getUserNameFromID(resultSet.getInt("owner_id")));
				discussionCommentsModel.setDiscussion_id(resultSet.getInt("discussion_id"));
				discussionCommentsModel.setComment(resultSet.getString("comment"));
				discussionCommentsModel.setCreatedtime(resultSet.getTimestamp("createdtime"));
				discussionCommentsModel.setLastmodified(resultSet.getTimestamp("lastmodified"));
				
				resultDiscussionComments.add(discussionCommentsModel);
			}	
			
			connection.close();
			preparedStatement.close();
			resultSet.close();
				
			}catch(Exception ex){
				connection = null;
				preparedStatement = null;
				resultSet = null;
				
				ex.printStackTrace();
			}
		
		return resultDiscussionComments;
	}
	
	// Get list of user's id who has given most recent fifty discussion comments.
	public ArrayList<Integer> getLatestFiftyDiscussionsOwners(){
		
		ArrayList<Integer> ownerIdList = new ArrayList<Integer>();
		
		try{
			
			String getdiscussionCommentQuery = "SELECT owner_id FROM picvik_discussion_comments " +
					" ORDER BY lastmodified DESC LIMIT 50;";
			
			MyLog.log(getdiscussionCommentQuery);
			
			connection = com.picvik.util.MySqlConnection.getInstance().getConnection();
			
			preparedStatement = connection.prepareStatement(getdiscussionCommentQuery);
			resultSet = preparedStatement.executeQuery();
			
			while(resultSet.next()){
				ownerIdList.add(resultSet.getInt("owner_id"));
			}	
			
			connection.close();
			preparedStatement.close();
			resultSet.close();
				
			}catch(Exception ex){
				connection = null;
				preparedStatement = null;
				resultSet = null;
				
				ex.printStackTrace();
			}
		
		return ownerIdList;		
	}
	
	// Get list of discussion id which has most recent fifty discussion comments.
	public ArrayList<Integer> getLatestFiftyDiscussionsIds(){
			
		ArrayList<Integer> discussionIdList = new ArrayList<Integer>();
		
		try{
			
			String getdiscussionCommentQuery = "SELECT discussion_id FROM picvik_discussion_comments " +
					" ORDER BY lastmodified DESC LIMIT 50;";
			
			MyLog.log(getdiscussionCommentQuery);
			
			connection = com.picvik.util.MySqlConnection.getInstance().getConnection();
			
			preparedStatement = connection.prepareStatement(getdiscussionCommentQuery);
			resultSet = preparedStatement.executeQuery();
			
			while(resultSet.next()){
				discussionIdList.add(resultSet.getInt("discussion_id"));
			}	
			
			connection.close();
			preparedStatement.close();
			resultSet.close();
				
			}catch(Exception ex){
				connection = null;
				preparedStatement = null;
				resultSet = null;
				
				ex.printStackTrace();
			}
		
		return discussionIdList;		
	}
	
	// Get a discussion comment with a given discusioncommentid.
	public String getDiscussionCommentWithId(int discussionCommentId){
		
		String comment = "";
		
		try{			
			String getdiscussionCommentQuery = "SELECT comment FROM picvik_discussion_comments WHERE " +
					"id =  "+ discussionCommentId +";";
			
			MyLog.log(getdiscussionCommentQuery);
			
			connection = com.picvik.util.MySqlConnection.getInstance().getConnection();
			
			preparedStatement = connection.prepareStatement(getdiscussionCommentQuery);
			resultSet = preparedStatement.executeQuery();
			
			comment = resultSet.getString("comment");
			
			connection.close();
			preparedStatement.close();
			resultSet.close();
				
			}catch(Exception ex){
				connection = null;
				preparedStatement = null;
				resultSet = null;
				
				ex.printStackTrace();
			}

		return comment;
	}
	
	// Get latest fifty comments objects, might be used in activity feeds.
	public ArrayList<DiscussionCommentsModel> getRecentFiftyDiscussionComments(){
		
		ArrayList<DiscussionCommentsModel> resultDiscussionComments = new ArrayList<DiscussionCommentsModel>();
		
		try{
		
			String getdiscussionCommentQuery = "SELECT * FROM picvik_discussion_comments " +
					" ORDER BY lastmodified DESC LIMIT 50;";
			
			MyLog.log(getdiscussionCommentQuery);
			
			connection = com.picvik.util.MySqlConnection.getInstance().getConnection();
			
			preparedStatement = connection.prepareStatement(getdiscussionCommentQuery);
			resultSet = preparedStatement.executeQuery();
			
			while(resultSet.next()){
				DiscussionCommentsModel discussionCommentsModel = new DiscussionCommentsModel();
				
				
				discussionCommentsModel.setId(resultSet.getInt("id"));
				discussionCommentsModel.setOwner_id(resultSet.getInt("owner_id"));
				discussionCommentsModel.setUsername(UserModel.getUserNameFromID(resultSet.getInt("owner_id")));
				discussionCommentsModel.setDiscussion_id(resultSet.getInt("discussion_id"));
				discussionCommentsModel.setComment(resultSet.getString("comment"));
				discussionCommentsModel.setCreatedtime(resultSet.getTimestamp("createdtime"));
				discussionCommentsModel.setLastmodified(resultSet.getTimestamp("lastmodified"));
				
				resultDiscussionComments.add(discussionCommentsModel);
			}	
			
			connection.close();
			preparedStatement.close();
			resultSet.close();
				
			}catch(Exception ex){
				connection = null;
				preparedStatement = null;
				resultSet = null;
				
				ex.printStackTrace();
			}
		
		return resultDiscussionComments;
	}
}
